package com.SRMS.service;

import com.SRMS.dao.*;
import com.SRMS.domain.*;
import com.SRMS.utils.JDBCUtilsByDruid;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * @author Albert
 * @version 1.0
 * @date 2023/12/16-16:23
 * @describe 学生服务类
 */
public class StudentService {
    private Student student;
    private StudentDAO studentDAO = new StudentDAO();
    private RoomForStudentDAO roomForStudentDAO = new RoomForStudentDAO();
    private RightToUseStudyRoomDAO rightToUseStudyRoomDAO = new RightToUseStudyRoomDAO();
    private StudyRoomUsageRecordDAO studyRoomUsageRecordDAO = new StudyRoomUsageRecordDAO();
    private HistoryForStudentDAO historyForStudentDAO = new HistoryForStudentDAO();
    private StudyRoomDAO studyRoomDAO = new StudyRoomDAO();

    /**
     * 根据学号查询历史使用记录
     * @param studentId 学号
     * @return 历史使用记录列表
     */
    public List<HistoryForStudent> getHistoryForStudentByStudentId(String studentId){
        return historyForStudentDAO.queryMulti("SELECT * FROM historyForStudent WHERE studentId = ?", HistoryForStudent.class, studentId);
    }

    /**
     * 归还使用权
     * @param rightToUseStudyRoom 使用权
     * @return 归还结果true/false
     */
    public boolean returnRight(RightToUseStudyRoom rightToUseStudyRoom){
        int result1 = 0;
        int result2 = 0;
        int result3 = 0;
        StudyRoom studyRoom = getStudyRoomById(rightToUseStudyRoom.getStudyRoomId());
        int remainingSeats = studyRoom.getRemainingSeats();
        //获取申请时间
        LocalDateTime ldt = getAppliedStudyRoom(rightToUseStudyRoom.getStudentId()).getApplicationTime().toLocalDateTime();
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String applicationTime = dateTimeFormatter.format(ldt);
        //获取归还时间
        LocalDateTime ldt2 = LocalDateTime.now();
        String discontinuingUseTime = dateTimeFormatter.format(ldt2);
        Connection connection = null;
        String sql1 = "UPDATE studyRoom SET remainingSeats = ? WHERE studyRoomId = ?";
        String sql2 = "INSERT INTO studyRoomUsageRecord VALUES (?, ?, ?, ?, ?)";
        String sql3 = "DELETE FROM rightToUseStudyRoom WHERE studentId = ?";
        PreparedStatement preparedStatement = null;
        //使用事务保证数据一致性
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //将connection设置为不自动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, ++remainingSeats);
            preparedStatement.setString(2, studyRoom.getStudyRoomId());
            result1 = preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1, studyRoom.getStudyRoomId());
            preparedStatement.setString(2, rightToUseStudyRoom.getStudentId());
            preparedStatement.setString(3, applicationTime);
            preparedStatement.setString(4, discontinuingUseTime);
            preparedStatement.setString(5, rightToUseStudyRoom.getRightId());
            result2 = preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setString(1, rightToUseStudyRoom.getStudentId());
            result3 = preparedStatement.executeUpdate();
            if(result1 == 0 || result2 == 0 || result3 == 0){
                //撤销回滚
                try {
                    connection.rollback();//默认回滚到事务开始时的状态
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }

                return false;
            }
            //提交事务
            connection.commit();

        } catch (SQLException e) {
            //撤销回滚
            try {
                connection.rollback();//默认回滚到事务开始时的状态
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }

            throw new RuntimeException(e);

        } finally {
            JDBCUtilsByDruid.close(null, preparedStatement, connection);
        }

        return true;
    }

    /**
     * 根据学号查询自习室使用权
     * @param studentId 学号
     * @return 返回使用权对象
     */
    public RightToUseStudyRoom getRightByStuId(String studentId){
        return rightToUseStudyRoomDAO.querySingle("SELECT * FROM rightToUseStudyRoom WHERE studentId = ?", RightToUseStudyRoom.class, studentId);
    }

    /**
     * 根据学生的学号查询其所申请到的自习室
     * @param studentId 学号
     * @return RoomForStudent申请到的学生可查看的自习室对象
     */
    public RoomForStudent getAppliedStudyRoom(String studentId){
        return roomForStudentDAO.querySingle("SELECT * FROM roomForStudent WHERE studentId = ?", RoomForStudent.class, studentId);
    }

    /**
     * 根据自习室编号为指定学生申请自习室
     * @param remainingSeats 申请前自习室剩余座位
     * @param studyRoomId 自习室编号
     * @param studentId 学号
     * @return 返回申请结果true/false
     */
    public boolean applyStudyRoom(int remainingSeats, String studyRoomId, String studentId){
        int result1 = 0;
        int result2 = 0;
        remainingSeats--;
        //设计rightId为学号后四位+年月日时分秒
        String datetime = LocalDateTime.now().toString();
        datetime = datetime.replace("-", "");
        datetime = datetime.replace("T", "");
        datetime = datetime.replace(":", "");
        datetime = datetime.substring(0, 14);
        String rightId = studentId.substring(6, 10) + datetime;

        //获取申请时间
        LocalDateTime ldt = LocalDateTime.now();
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String applicationTime = dateTimeFormatter.format(ldt);
        Connection connection = null;
        String sql1 = "UPDATE studyRoom SET remainingSeats = ? WHERE studyRoomId = ?";
        String sql2 = "INSERT INTO rightToUseStudyRoom VALUES (?, ?, ?, ?)";
        PreparedStatement preparedStatement = null;
        //使用事务保证数据一致性
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //将connection设置为不自动提交
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, remainingSeats);
            preparedStatement.setString(2, studyRoomId);
            result1 = preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1, rightId);
            preparedStatement.setString(2, studentId);
            preparedStatement.setString(3, studyRoomId);
            preparedStatement.setString(4, applicationTime);
            result2 = preparedStatement.executeUpdate();
            if(result1 == 0 || result2 == 0){
                //撤销回滚
                try {
                    connection.rollback();//默认回滚到事务开始时的状态
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }

                return false;
            }
            //提交事务
            connection.commit();

        } catch (SQLException e) {
            //撤销回滚
            try {
                connection.rollback();//默认回滚到事务开始时的状态
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }

            throw new RuntimeException(e);

        } finally {
            JDBCUtilsByDruid.close(null, preparedStatement, connection);
        }

        return true;
    }

    /**
     * 根据自习室编号查询自习室
     * @param studyRoomId
     * @return 返回查询到的自习室
     */

    public StudyRoom getStudyRoomById(String studyRoomId){
        return studyRoomDAO.querySingle("SELECT * FROM studyRoom WHERE studyRoomId=?", StudyRoom.class, studyRoomId);
    }

    /**
     * 查询所有的自习室信息
     * @return 返回自习室列表
     */
    public List<StudyRoom> studyRoomList(){
        return studyRoomDAO.queryMulti("SELECT * FROM studyRoom", StudyRoom.class);
    }

    /**
     * 根据学号和密码查找学生
     * @param studentId 学号
     * @param password 密码
     * @return 若找到，返回该学生对象；若没找到，返回null
     */
    public Student getStudentByIdAndPwd(String studentId, String password){
        student = studentDAO.querySingle("SELECT * FROM student WHERE studentId=? AND password=MD5(?)", Student.class, studentId, password);
        return student;
    }

    /**
     * 该方法可以获取本Service保存的Student对象
     * @return 返回Student对象
     */
    public Student getStudent() {
        return student;
    }
}
